﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Oracle.ManagedDataAccess.Client;

namespace bmapi.Models.DataEntity
{

    public class SQLCommand
    {
        public static readonly string connstr = ConfigurationManager.ConnectionStrings["connectStr"].ToString();
        public static readonly bool IsOracle = Convert.ToBoolean(ConfigurationManager.AppSettings["IsOracle"]);
        private  string m_paras = "";

        #region  数据库访问

        public DbCommand cmd = null;
        public SQLCommand()
        {
            cmd = SqlORLCmd();
        }
        public SQLCommand(string sqlcmd)
        {
            if (IsOracle)
            {
                sqlcmd = sqlcmd.Replace("@", ":");
                cmd = new OracleCommand(sqlcmd);
            }
            else
                cmd = new SqlCommand(sqlcmd);
        }
        public SQLCommand(string sqlcmd, string parameterName, object value)
        {
            cmd = SqlORLCmd();
            if (IsOracle)
            {
                cmd.CommandText = sqlcmd.Replace("@", ":");
                parameterName = parameterName.Replace("@", ":");
                cmd.Parameters.Add(new OracleParameter(parameterName, value));
            }
            else
            {
                cmd.CommandText = sqlcmd;
                cmd.Parameters.Add(new SqlParameter(parameterName, value));
            }
        }
        public void Add(string parameterName, object value, bool isTime = false)
        {
            if (isTime && IsOracle)
                value = Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss");
            if (IsOracle)
            {
                parameterName = parameterName.Replace("@", ":");
                cmd.Parameters.Add(new OracleParameter(parameterName, value));
            }
            else
            {
                cmd.Parameters.Add(new SqlParameter(parameterName, value));
            }
        }
        public void AddInsert(string parameterName, object value)
        {
            if (IsOracle)
            {
                parameterName = parameterName.Replace("@", ":");
                cmd.Parameters.Add(new OracleParameter(parameterName, value));
                m_paras += parameterName + ",";
            }
            else
            {
                cmd.Parameters.Add(new SqlParameter(parameterName, value));
                m_paras += parameterName + ",";
            }
        }
        public void AddTimeInsert(string parameterName, object value)
        {
            if (IsOracle)
            {
                parameterName = parameterName.Replace("@", ":");
                m_paras += string.Format("to_date({0},'yyyy-mm-dd hh24:mi:ss')", parameterName) + ",";
                value = Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss");
                cmd.Parameters.Add(new OracleParameter(parameterName, value));
            }
            else
            {
                m_paras += parameterName + ","; 
                cmd.Parameters.Add(new SqlParameter(parameterName, value));
            }
        }

        public void JoinSql(string sql)
        {
            cmd.CommandText=sql+ m_paras.Remove(m_paras.LastIndexOf(",")) + ")";
            m_paras = "";
        }

        /// <summary>
        /// 非查询
        /// </summary>
        /// <returns></returns>
        public int ExecuteNonQuery()
        {
            DbConnection conn = Connection();
            cmd.Connection = conn;
            int result = cmd.ExecuteNonQuery();
            CloseDb(conn);
            return result;
        }
        #region  查询 
        /// <summary>
        /// 多个值查询
        /// </summary>
        /// <returns>dt</returns>
        public DataTable ExecuteScalars()
        {
            DbConnection conn = Connection();
            cmd.Connection = conn;
            DbDataReader dataReader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            if (dataReader.HasRows)
                dt.Load(dataReader);
            CloseDb(conn);
            if (dt.Rows.Count > 0)
                return dt;
            else
                return null;

        }
        /// <summary>
        /// 单行查询
        /// </summary>
        /// <returns></returns>
        public DataRow ExecuteOneRow()
        {
            DbConnection conn = Connection();
            cmd.Connection = conn;
            DbDataReader dataReader = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            if (dataReader.HasRows)
                dt.Load(dataReader);
            CloseDb(conn);
            if (dt.Rows.Count > 0)
                return dt.Rows[0];
            else
                return null;
        }


        /// <summary>
        /// 返回一个值 
        /// </summary>
        /// <returns></returns>
        public string ExecuteScalar()
        {
            DbConnection conn = Connection();
            cmd.Connection = conn;
            Object obj = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            CloseDb(conn);
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return null;
            }
        }

        #endregion
        #region  执行事务 
        /// <summary>  
        /// 执行事务，返回0或者1  
        /// </summary>  
        /// <param name="Sqlstr">sql语句</param>  
        /// <returns></returns>  
        public static int ExecTran(List<SQLCommand> sqlcmds)
        {
            int res = 0;
            DbConnection conn = Connection();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < sqlcmds.Count; i++)
                {
                    sqlcmds[i].cmd.Connection = conn;
                    sqlcmds[i].cmd.Transaction = tran;
                    res = sqlcmds[i].cmd.ExecuteNonQuery();
                    sqlcmds[i].cmd.Parameters.Clear();
                }
                tran.Commit();
                return 1;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                return 0;
            }
            finally
            {
                conn.Close();
                tran.Dispose();
            }
        }
        #endregion 
        #region 获取分页的数据
        /// <summary>
        /// 获取分页的数据
        /// </summary>
        /// <param name="TblName">数据表名</param>
        /// <param name="Fields">要读取的字段</param>
        /// <param name="OrderField">排序字段 也可后接升降序 例：Time desc</param>
        /// <param name="SqlWhere">查询条件</param>
        /// <param name="PageSize">每页显示多少条数据</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="TotalPage">返回值，共有多少页</param>
        /// <param name="TotalRecord">返回值，总有多少条记录</param>
        /// <returns></returns>
        public static void GetDataListByPage(string TblName, string Fields, string OrderField, string SqlWhere, int PageSize, int pageIndex, out int TotalPage, out int TotalRecord, out DataTable dt)
        {
            if (IsOracle)
                ORLGetDataListByPage(TblName, Fields, OrderField, SqlWhere, PageSize, pageIndex, out TotalPage, out TotalRecord, out dt);
            else
                SqlGetDataListByPage(TblName, Fields, OrderField, SqlWhere, PageSize, pageIndex, out TotalPage, out TotalRecord, out dt);
        }


        /// <summary>
        /// ORL获取分页的数据
        /// </summary>
        /// <param name="TblName">数据表名</param>
        /// <param name="Fields">要读取的字段</param>
        /// <param name="OrderField">排序字段 也可后接升降序 例：Time desc</param>
        /// <param name="SqlWhere">查询条件</param>
        /// <param name="PageSize">每页显示多少条数据</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="TotalPage">返回值，共有多少页</param>
        /// <param name="TotalRecord">返回值，总有多少条记录</param>
        /// <returns></returns>
        public static void ORLGetDataListByPage(string TblName, string Fields, string OrderField, string SqlWhere, int PageSize, int pageIndex, out int TotalPage, out int TotalRecord, out DataTable dt)
        {
            OracleConnection conn = new OracleConnection(connstr);
            OracleCommand cmd = new OracleCommand("proc_GetListByPage", conn);

            #region
            //注意这里的参数名和类型号与存储过程里面的一样
            cmd.Parameters.Add(new OracleParameter(":tableName", OracleDbType.Varchar2)).Value = TblName;
            cmd.Parameters.Add(new OracleParameter(":fields", OracleDbType.Varchar2)).Value = Fields;
            cmd.Parameters.Add(new OracleParameter(":wherecase", OracleDbType.Varchar2)).Value = SqlWhere;
            cmd.Parameters.Add(new OracleParameter(":pageSize", OracleDbType.Int32)).Value = PageSize;
            cmd.Parameters.Add(new OracleParameter(":pageNow", OracleDbType.Int32)).Value = pageIndex;
            cmd.Parameters.Add(new OracleParameter(":orderField", OracleDbType.Varchar2)).Value = OrderField;

            //记录总数
            cmd.Parameters.Add(new OracleParameter(":myrows", OracleDbType.Int32));
            cmd.Parameters[":myrows"].Direction = ParameterDirection.Output;
            //总页数
            cmd.Parameters.Add(new OracleParameter(":myPageCount", OracleDbType.Int32));
            cmd.Parameters[":myPageCount"].Direction = ParameterDirection.Output;
            //返回的记录集
            cmd.Parameters.Add(new OracleParameter(":p_cursor", OracleDbType.RefCursor));
            cmd.Parameters[":p_cursor"].Direction = ParameterDirection.Output;
            #endregion

            cmd.CommandType = CommandType.StoredProcedure;

            OracleDataAdapter dataAdapter = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);

            TotalPage = int.Parse(cmd.Parameters[":myPageCount"].Value.ToString());
            TotalRecord = int.Parse(cmd.Parameters[":myrows"].Value.ToString());


            dt = ds.Tables[0];
            if (dt.Rows.Count < 1)
                dt = null;
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
        }

        #region SQL获取分页的数据
        /// <summary>
        /// 获取分页的数据
        /// </summary>
        /// <param name="TblName">数据表名</param>
        /// <param name="Fields">要读取的字段</param>
        /// <param name="OrderField">排序字段 也可后接升降序 例：Time desc</param>
        /// <param name="SqlWhere">查询条件</param>
        /// <param name="PageSize">每页显示多少条数据</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="TotalPage">返回值，共有多少页</param>
        /// <param name="TotalRecord">返回值，总有多少条记录</param>
        /// <returns></returns>
        public static void SqlGetDataListByPage(string TblName, string Fields, string OrderField, string SqlWhere, int PageSize, int pageIndex, out int TotalPage, out int TotalRecord, out DataTable dt)
        {

            SqlConnection conn = new SqlConnection(connstr);
            SqlCommand cmd = new SqlCommand("proc_GetListByPage", conn);
            #region
            cmd.Parameters.Add(new SqlParameter("@TableName", SqlDbType.NVarChar)).Value = TblName;

            cmd.Parameters.Add(new SqlParameter("@Fields", SqlDbType.NVarChar)).Value = Fields;
            cmd.Parameters.Add(new SqlParameter("@OrderField", SqlDbType.NVarChar)).Value = OrderField;
            cmd.Parameters.Add(new SqlParameter("@sqlWhere", SqlDbType.NVarChar,800)).Value = SqlWhere;
            cmd.Parameters.Add(new SqlParameter("@pageSize", SqlDbType.Int)).Value = PageSize;
            cmd.Parameters.Add(new SqlParameter("@pageIndex", SqlDbType.Int)).Value = pageIndex;
            cmd.Parameters.Add(new SqlParameter("@TotalPage", SqlDbType.Int));

            cmd.Parameters["@TotalPage"].Direction = ParameterDirection.Output;//获得out出来的参数值

            cmd.Parameters.Add(new SqlParameter("@totalRecord", SqlDbType.Int));
            cmd.Parameters["@totalRecord"].Direction = ParameterDirection.Output;
            #endregion

            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);

            string pages = cmd.Parameters["@TotalPage"].Value.ToString();
            string rows = cmd.Parameters["@totalRecord"].Value.ToString();
            if (string.IsNullOrEmpty(pages) || string.IsNullOrEmpty(rows))
            {
                TotalPage = 0;
                TotalRecord = 0;
                dt = null;
            }
            else
            {

                TotalPage = Convert.ToInt32(pages);
                TotalRecord = Convert.ToInt32(rows);
            }
            conn.Close();
            conn.Dispose();
            cmd.Dispose();
            dt = ds.Tables[0];
            if (dt.Rows.Count < 1)
                dt = null;
        }
        #endregion

        #endregion

        private static DbConnection Connection()
        {
            if (IsOracle)
            {
                OracleConnection conn = new OracleConnection(connstr);
                conn.Open();
                return conn;
            }
            else
            {
                SqlConnection sqlConn = new SqlConnection(connstr);
                sqlConn.Open();
                return sqlConn;
            }
        }

        private static void CloseDb(DbConnection conn)
        {
            conn.Close();
        }

        private DbCommand SqlORLCmd()
        {
            if (IsOracle)
                cmd = new OracleCommand();
            else
                cmd = new SqlCommand();
            return cmd;
        }
        #endregion


    }
}